from typing import Tuple, Union

import pandas as pd
import numpy as np
from pandas import DataFrame

from app.utils.common_func_defs import *
from app.services import get_engine


engine = get_engine()


def display_uploaded0(df, table_zh_name:str):
    df = uploaded_field_corr_entozh_res(df, table_zh_name)
    df = df.fillna('nan')
    return df


def get_deliver_sale_bag_month_detail(start_date: str, end_date: str):
    df_sell_info = pd.read_sql_query(
        f"SELECT dsbm.*, ci.attribute, ci.store_name, pi.brand_name, pi.parent_series, pi.child_series FROM deliver_sale_bag_month AS dsbm LEFT JOIN customer_information AS ci ON dsbm.customer_code = ci.customer_code LEFT JOIN product_information AS pi ON dsbm.material_code = pi.material_code WHERE dsbm.stat_time BETWEEN '{start_date}' AND '{end_date}'", engine)
    if df_sell_info[df_sell_info['stat_time'] == end_date].empty:
        raise InnerReportException('结束时间对应月份的所有销售情况未上传')
    if df_sell_info is None or df_sell_info.empty:
        raise InnerReportException('查询结果为空')
    df_sell_info['the_amount'] = df_sell_info.apply(lambda row: row['A_price_amount'] if row['attribute']=='实销' else row['deliver_amount'], axis=1)
    return df_sell_info


def get_sub_table(df: pd.DataFrame, prefix: str):
    tp1 = df.groupby(['brand_name', 'parent_series', 'child_series']).apply(lambda group: group['the_amount'].sum() / 10000).reset_index(name='ship_initial')
    tp2 = df.groupby(['brand_name', 'parent_series', 'child_series']).apply(lambda group: group['deliver_amount'].sum() / 10000).reset_index(name='sell')
    tp = pd.merge(tp1, tp2, how='left', on=['brand_name', 'parent_series', 'child_series'])
    tp['discount'] = tp['ship_initial'] - tp['sell']
    tp['sell_percent'] = tp['ship_initial'] / tp['ship_initial'].sum()
    tp['ship_initial'] = tp['ship_initial'].round(2)
    tp['sell'] = tp['sell'].round(2)
    tp['discount'] = tp['discount'].round(2)
    tp['sell_percent'] = tp['sell_percent'].round(2)
    tp = display_uploaded0(tp, '品项销售月统计表')
    # 添加前缀
    tp = tp.add_prefix(prefix)
    # 更新表头
    tp = tp.rename(columns={f"{prefix}品牌名称": '品牌名称', f"{prefix}大系列": '大系列', f"{prefix}子系列": '子系列'})
    return tp


def pinxiang(start_date, end_date, target_store_name) -> DataFrame:
    df_sale_detail = get_deliver_sale_bag_month_detail(start_date, end_date)
    # 若查询无结果，则直接返回空
    if df_sale_detail is None or df_sale_detail.empty:
        return pd.DataFrame()
    # 计算全店铺累计数据
    tb1 = get_sub_table(df_sale_detail, '全店铺累计')
    # 计算全店铺当前数据
    _df_total_this = df_sale_detail.query(f"stat_time=='{end_date}'")
    if _df_total_this is None or _df_total_this.empty:
        return tb1
    tb2 = get_sub_table(_df_total_this,'全店铺当前')
    # 未指定店铺，则指定店铺的统计值为空
    if target_store_name is None or target_store_name == '':
        return tb1.merge(tb2, on=['品牌名称','大系列','子系列'], how='left')
    # 未获取到指定店铺在指定结束时间内的数据，则指定店铺的统计值为空
    elif not ((df_sale_detail['store_name'] == target_store_name) & (df_sale_detail['stat_time'] == end_date)).any():
        return tb1.merge(tb2, on=['品牌名称','大系列','子系列'], how='left')
    else:
        # 计算指定累计数据
        _df_this_total = df_sale_detail.query(f"store_name =='{target_store_name}'")
        # 未获取到指定店铺的数据，则指定店铺的统计值为空
        if _df_this_total is None or _df_this_total.empty:
            return tb1.merge(tb2, on=['品牌名称','大系列','子系列'], how='left')
        tb3 = get_sub_table(_df_this_total, '此店铺累计')
        tb3['店铺'] = target_store_name
        # 计算指定当前数据
        _df_this_this = _df_this_total.query(f"stat_time=='{end_date}'")
        if _df_this_this is None or _df_this_this.empty:
            return tb1.merge(tb2, on=['品牌名称','大系列','子系列'], how='left').merge(tb3, on=['品牌名称','大系列','子系列'], how='left')
        tb4 = get_sub_table(_df_this_this, '此店铺当前')
        return tb1.merge(tb2, on=['品牌名称','大系列','子系列'], how='left').merge(tb3, on=['品牌名称','大系列','子系列'], how='left').merge(tb4, on=['品牌名称','大系列','子系列'], how='left')


def pinxiang_exe(start_date, end_date, target_store_name=''):
    # 将年份减1
    last_year_start_date = str(int(start_date[:4]) - 1)
    last_year_end_date = str(int(end_date[:4]) - 1)
    # 拼接新的字符串变量
    last_year_start_date += start_date[4:]
    last_year_end_date += end_date[4:]
    # 计算目标年份的情况
    target_year = None
    try:
        target_year = pinxiang(start_date, end_date, target_store_name)
    except InnerReportException as e:
        print(str(e)+" when calc targetYear")
    if target_year is None or target_year.empty:
        return DataFrame()
    # 计算前一年同时期的情况
    last_year = None
    try:
        last_year = pinxiang(last_year_start_date, last_year_end_date, target_store_name)
    except InnerReportException as e:
        print(str(e)+" when calc lastYear")
    if last_year is not None and not last_year.empty:
        last_year = last_year.loc[:, ['品牌名称', '大系列', '子系列', '店铺','此店铺累计销售', '此店铺累计销售占比', '此店铺当前销售', '此店铺当前销售占比', '全店铺累计销售', '全店铺累计销售占比', '全店铺当前销售', '全店铺当前销售占比']]
        last_year = last_year.add_prefix('last_year_')
        last_year = last_year.tp.rename(columns={"last_year_品牌名称": '品牌名称', "last_year_大系列": '大系列', "last_year_子系列": '子系列', "last_year_店铺": "店铺"})
        if all(column_name in ['品牌名称','大系列','子系列'] for column_name in last_year.columns):
            if "店铺" in target_year.columns and "店铺" in last_year.columns:
                target_year = target_year.merge(last_year, on=['品牌名称','大系列','子系列','店铺'], how='left')
            else:
                target_year = pd.merge(target_year, last_year, on=['品牌名称','大系列','子系列'], how='left')
    for prefixTag in ["此店铺累计", "此店铺当前", "全店铺累计", "全店铺当前"]:
        if f"{prefixTag}销售" in target_year.columns and f"last_year_{prefixTag}销售" in target_year.columns:
            target_year[f"{prefixTag}销售同比"] = target_year[f"{prefixTag}销售"] - target_year[f"last_year_{prefixTag}销售"]
        if f"{prefixTag}销售占比" in target_year.columns and f"last_year_{prefixTag}销售占比" in target_year.columns:
            target_year[f"{prefixTag}占比涨跌同比"] = target_year[f"{prefixTag}销售占比"] - target_year[f"last_year_{prefixTag}销售占比"]
    # 批量删除指定前缀的列
    columns_to_drop = target_year.filter(regex=f'^last_year_').columns
    if columns_to_drop is not None and not columns_to_drop.empty:
        target_year = target_year.drop(columns_to_drop, axis=1)
    if target_year is None or target_year.empty:
        return DataFrame()
    return target_year.reset_index(drop=True).sort_values(['品牌名称','大系列','子系列'])
